package com.cnvp.dbcompare.biz;

import com.cnvp.dbcompare.pojo.bo.IndexBo;
import com.cnvp.dbcompare.pojo.bo.TableBo;
import com.cnvp.dbcompare.constant.CommonConst;
import com.cnvp.dbcompare.pojo.bo.FieldBo;
import com.cnvp.dbcompare.util.FieldUtil;
import com.cnvp.dbcompare.util.Tools;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Objects;


/**
 * 数据库 业务工具类
 * @author suqiongbang
 * @date 2021/10/22 19:38
 */
@Component
public class DatabaseBiz {

    /**
     * 获取所有表结构
     *
     * @param key   数据库连接信息key
     * @throws Exception exception
     */
    public LinkedHashMap<String, TableBo> getTableStruct(Integer key){
        Connection conn = Tools.getConnection(key);
        try {
            ResultSet rs = commonQuery(conn, CommonConst.MySql.QRY_SHOW_TABLES);
            // 获取数据库下所有表名
            List<String> tableNameList = obtainTableNameList(rs);
            // 遍历数据库下所有表的结构
            LinkedHashMap<String, TableBo> tableNameAndTableBodyMap = new LinkedHashMap<>();
            for (String tableName : tableNameList) {
                ResultSet rs2 = commonQuery(conn, CommonConst.MySql.QRY_DESCRIBE + tableName);
                LinkedHashMap<String, FieldBo> fieldLinkedHashMap = obtainTableBody(rs2);

                ResultSet rs3 = commonQuery(conn, CommonConst.MySql.QRY_SHOW_INDEX + tableName);
                LinkedHashMap<String, IndexBo> indexLinkedHashMap = obtainTableIndex(rs3);

                // 构造表模型实体
                TableBo body = new TableBo();
                body.setTableName(tableName);
                body.setFieldMap(fieldLinkedHashMap);
                body.setIndexMap(indexLinkedHashMap);
                tableNameAndTableBodyMap.put(tableName, body);
            }
            return tableNameAndTableBodyMap;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 通用查询方法，返回结果集
     *
     * @param conn
     * @param sql
     * @return ResultSet
     * @throws SQLException
     */
    public ResultSet commonQuery(Connection conn, String sql) throws SQLException {
        Statement stmt = Objects.requireNonNull(conn).createStatement();
        return stmt.executeQuery(sql);
    }

    /**
     * 获取数据库表名列表的私有处理方法
     *
     * @param rs
     * @return
     * @throws SQLException
     */
    private List<String> obtainTableNameList(ResultSet rs) throws SQLException {
        List<String> list = new ArrayList<>();
        while (rs.next()) {
            list.add(rs.getString(1));
        }
        return list;
    }

    /**
     * 根据结果集获取单表内所有字段模型
     * @param rs
     * @return
     * @throws SQLException
     */
    private LinkedHashMap<String, FieldBo> obtainTableBody(ResultSet rs) throws SQLException {
        LinkedHashMap<String, FieldBo> fieldMap = new LinkedHashMap<>();
        while (rs.next()) {
            FieldBo field = new FieldBo();
            // 此处 rs 的取值范围 1~9
            String rsField = rs.getString(1);
            String rsType = rs.getString(2);
            String rsNull = rs.getString(4);
            String rsKey = rs.getString(5);
            String rsDefault = rs.getString(6);
            String rsExtra = rs.getString(7);
            String rsComment = rs.getString(9);
            // 转化值
            field.setName(rsField);
            FieldUtil.handleTableField4Type(field, rsType);
            field.setDefaultValue(rsDefault);
            field.setComment(rsComment);
            FieldUtil.handleTableField4Null(field, rsNull);
            FieldUtil.handleTableField4AutoIncr(field, rsExtra);

            fieldMap.put(rsField, field);
        }

        return fieldMap;
    }

    /**
     * 根据结果集获取单表内所有索引
     * @param rs
     * @return
     * @throws SQLException
     */
    private LinkedHashMap<String, IndexBo> obtainTableIndex(ResultSet rs) throws SQLException {
        LinkedHashMap<String, IndexBo> indexMap = new LinkedHashMap<>();
        while (rs.next()) {
            IndexBo indexBo = new IndexBo();
            // 此处 rs 取值范围 1~15
            indexBo.setTable(rs.getString(1));
            indexBo.setNonUnique(Integer.valueOf(rs.getString(2)));
            indexBo.setKeyName(rs.getString(3));
            indexMap.put(indexBo.getKeyName(), indexBo);
        }
        return indexMap;
    }
}
